﻿// -----------------------------------------------------------------------
// <copyright file="CommentData.cs" company="VDC-IT" author="LTDUC">
//      Copyright (c) VDC-IT. All rights reserved.
// </copyright>
// -----------------------------------------------------------------------

namespace VDC.VONE.DAL
{
    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Reflection;
    using Microsoft.ApplicationBlocks.Data;
    using VDC.COMMON;
    using VDC.VONE.ENTITIES;
    
    /// <summary>
    /// Class access to db Comments
    /// </summary>
    public class CommentData
    {
        /// <summary>
        /// Get Comments By Id
        /// </summary>
        /// <param name="commentId">Param commentId</param>
        /// <returns>Return Comments</returns>
        public CommentEN GetCommentById(int commentId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                CommentEN comment = null;
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString,Constants.StorepProcedure.VOneCommentsGetById, commentId);
                while (reader.Read())
                {
                    comment = new CommentEN();

                    if (!reader.IsDBNull(0))
                    {
                        comment.CommentsId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        comment.ProfileId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        comment.UserId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        comment.Date = reader.GetDateTime(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        comment.Content = reader.GetString(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        comment.Attachments = reader.GetString(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        comment.Role = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        comment.DepartmentId = reader.GetInt32(7);
                    }
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return comment;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get list all Comments
        /// </summary>
        /// <returns>Return list all Comments</returns>
        public IList<CommentEN> GetComments()
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<CommentEN> lstComment = new List<CommentEN>();
                SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCommentsGetAll);
                while (reader.Read())
                {
                    CommentEN comment = new CommentEN();

                    if (!reader.IsDBNull(0))
                    {
                        comment.CommentsId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        comment.ProfileId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        comment.UserId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        comment.Date = reader.GetDateTime(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        comment.Content = reader.GetString(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        comment.Attachments = reader.GetString(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        comment.Role = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        comment.DepartmentId = reader.GetInt32(7);
                    }

                    lstComment.Add(comment);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstComment;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Insert to db Comments
        /// </summary>
        /// <param name="comment">Paarm Comments</param>
        /// <returns>Insert Comments</returns>
        public int Insert(CommentEN comment)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.Comment.ProfileId, comment.ProfileId),
                                    new SqlParameter(Constants.SqlParamerterName.Comment.UserId, comment.UserId),
                                    new SqlParameter(Constants.SqlParamerterName.Comment.Date, (object)comment.Date ?? DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.Comment.Content, comment.Content),
                                    new SqlParameter(Constants.SqlParamerterName.Comment.Attachments, comment.Attachments),
                                    new SqlParameter(Constants.SqlParamerterName.Comment.Role, comment.Role),
                                    new SqlParameter(Constants.SqlParamerterName.Comment.DepartmentId, comment.DepartmentId)
                                };

                var result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCommentsInsert, sqlParameters));
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return result;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Update to db Comments
        /// </summary>
        /// <param name="comment">Param Comments</param>
        public void Update(CommentEN comment)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.Comment.CommentsId, comment.CommentsId),
                                    new SqlParameter(Constants.SqlParamerterName.Comment.ProfileId, comment.ProfileId),
                                    new SqlParameter(Constants.SqlParamerterName.Comment.UserId, comment.UserId),
                                    new SqlParameter(Constants.SqlParamerterName.Comment.Date, (object)comment.Date ?? DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.Comment.Content, comment.Content),
                                    new SqlParameter(Constants.SqlParamerterName.Comment.Attachments, comment.Attachments),
                                    new SqlParameter(Constants.SqlParamerterName.Comment.Role, comment.Role),
                                    new SqlParameter(Constants.SqlParamerterName.Comment.DepartmentId, comment.DepartmentId)
                                };

                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCommentsUpdate, sqlParameters);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Delete Comments by commentId
        /// </summary>
        /// <param name="commentId">param commentId</param>
        public void Delete(int commentId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCommentsDelete, commentId);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get List Comments By profileId
        /// </summary>
        /// <param name="profileId">Param profileId</param>
        /// <returns>Return List Comments</returns>
        public IList<CommentEN> GetCommentByProfileId(int profileId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<CommentEN> lstComment = new List<CommentEN>();
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCommentsGetByProfileId, profileId);
                while (reader.Read())
                {
                    CommentEN comment = new CommentEN();

                    if (!reader.IsDBNull(0))
                    {
                        comment.CommentsId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        comment.ProfileId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        comment.UserId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        comment.Date = reader.GetDateTime(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        comment.Content = reader.GetString(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        comment.Attachments = reader.GetString(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        comment.Role = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        comment.DepartmentId = reader.GetInt32(7);
                    }

                    lstComment.Add(comment);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstComment;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Gets the comment by user_ comment.
        /// </summary>
        /// <param name="userId">The user id.</param>
        /// <param name="commentId">The comment id.</param>
        /// <returns>return CommentEN</returns>
        public CommentEN GetCommentByUser_Comment(int userId, int commentId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                CommentEN comment = null;
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCommentsGetByUserComment, userId, commentId);
                while (reader.Read())
                {
                    comment = new CommentEN();

                    if (!reader.IsDBNull(0))
                    {
                        comment.CommentsId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        comment.ProfileId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        comment.UserId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        comment.Date = reader.GetDateTime(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        comment.Content = reader.GetString(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        comment.Attachments = reader.GetString(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        comment.Role = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        comment.DepartmentId = reader.GetInt32(7);
                    }
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return comment;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }
    }
}